clear
set more off

********************************************************************************
***** Project: The Short and Long Term Effects of In-Person Performance Feedback
********************************************************************************
***** A. R. Soetevent & G. J. Romensen
********************************************************************************
***** Cleaning and Preparing Coach Logs
********************************************************************************
***** Latest update: 06-09-2021
********************************************************************************
*global filepath "C:\JPEMicReplication"
log using "$filepath/Logs/02Coachlogs.log", replace
*** 1. Retrieve driver ids

import delimited "$filepath\Data\Randomisatie\RandomisatieFinal.csv"

rename rugnr chauf_nr_rug

sort chauf_nr_rug

levelsof chauf_nr_rug, local(levels_rugnr)
global levels_rugnr `levels_rugnr'

clear

import excel "$filepath\Data\Dagboeken coaches\Ecobox Coaches V2 - RUG v01.xlsx", firstrow

foreach var of varlist _all {
rename `var' `=lower("`var'")'
}

*** 2. Drop drivers that are not part of the study

gen rugcheck=.
foreach x of global levels_rugnr {
replace rugcheck=1 if chauf_nr_rug==`x'
}

drop if rugcheck!=1
drop rugcheck

*** 3. Dealing with no coaching date 

list jaar_week eco_coach_nr_rug if datum_begeleiding==.

sum datum_begeleiding if eco_coach_nr_rug==73 & jaar_week=="2015-46"
local jw46=round(r(mean))
sum datum_begeleiding if eco_coach_nr_rug==73 & jaar_week=="2015-47"
local jw47=round(r(mean))

replace datum_begeleiding=`jw46' if datum_begeleiding==. & jaar_week=="2015-46"
replace datum_begeleiding=`jw47' if datum_begeleiding==. & jaar_week=="2015-47"

*** 4. Determine driver-specific coaching dates

sort chauf_nr_rug datum_begeleiding

levelsof chauf_nr_rug, local(levels_chaufnr)

bysort chauf_nr_rug: gen samedriver=cond(_N==1,1,_n) 

sum samedriver
local maxcoaching=r(max)

foreach x of numlist 1(1)`maxcoaching'{
gen coachdatum_`x'=.
foreach y of local levels_chaufnr {
sum datum_begeleiding if chauf_nr_rug==`y' & samedriver==`x'
local `y'_`x'=r(mean)
replace coachdatum_`x'=``y'_`x'' if chauf_nr_rug==`y' 
format coachdatum_`x' %td
}
}

bysort chauf_nr_rug: keep if _n==1

gen flag=cond(datum_begeleiding<=date("1-1-2015", "DMY") | datum_begeleiding>=date("13-2-2017", "DMY"),1,0)
count if flag==1

keep chauf_nr_rug coachdatum_* eco_coach_nr_rug

*** 5. Saving coach logs 

save "$filepath\Data\Using databases\coachlogs.dta", replace
log close